USE Liberty
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
			WHERE SPECIFIC_SCHEMA = N'dbo'
			AND SPECIFIC_NAME = N'FindServiceMemberByNameDOBCase' 
			)
BEGIN
	DROP PROCEDURE dbo.FindServiceMemberByNameDOBCase
END
GO

CREATE PROCEDURE dbo.FindServiceMemberByNameDOBCase
@LastName		varchar(128)	= NULL,
@DOBMonth		varchar(02)		= NULL,
@DOBDay			varchar(02)		= NULL,
@DOBYear		varchar(04)		= NULL,
@CaseNumber		varchar(128)	= NULL

AS  

---------------------------------------------------------------------------------------------------
--	Stored Procedure: dbo.FindServiceMemberByNameDOBCase
--
--	Purpose: Performs a search on ServiceMen table based on parms passed in.
--
--	Dependencies:
---------------------------------------------------------------------------------------------------
-- Modifications
--	Date		Author				Description
---------------------------------------------------------------------------------------------------
--	2007-06-27	Harry Smith			ORIGINAL
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @error		int,
		@rowcount	int


DECLARE @select		varchar(2000),
		@where		varchar(2000),
		@from		varchar(2000)
DECLARE @wrkDOB		varchar(10)

CREATE TABLE #srchResults
(smOID			bigint
,smName			varchar(256)
,smDOB			varchar(10)
,City			varchar(128)
,State			varchar(128)
,Rank			varchar(50)
,Unit			varchar(50)
,Cases			varchar(50)	NULL
)


---------------------------------------------------------------------------------------------------
--	Step 1: Initialize variables / beginning housekeeping
---------------------------------------------------------------------------------------------------
SELECT @error = 0, @rowcount = 0


IF ISNULL(@LastName, '') = ''
AND ISNULL(@DOBMonth, '') = ''
AND ISNULL(@DOBDay, '') = ''
AND ISNULL(@DOBYear, '') = ''
AND ISNULL(@CaseNumber, '') = ''
BEGIN
	RAISERROR(N'At least one search value must be supplied', 10, 1)
	GOTO ExitError
END


-- Setup the main portion of the dynamic SQL statement
SET @select = 'SELECT v.OID, v.[Name], v.DOBFormatted, v.City, v.State, v.Rank, v.Unit '
SET @from = CHAR(10) + ' FROM dbo.vwServiceMenSearchResults v '
SET @where = ''


---------------------------------------------------------------------------------------------------
--	Step 2: Setup the initial filter of the WHERE clause
---------------------------------------------------------------------------------------------------
IF ISNULL(@LastName, '') <> ''
BEGIN
	SET @where = @where + ' WHERE v.[Name] LIKE ''' + @LastName + '%'' '
END

-- If an all three DOB elements were supplied, we can compare against the formatted DOB
IF (ISNULL(@DOBMonth, '') <> ''
AND ISNULL(@DOBDay, '') <> ''
AND ISNULL(@DOBYear, '') <> '')
BEGIN
	SET @wrkDOB = @DOBMonth + '/' + @DOBDay + '/' + @DOBYear
	IF @where <> ''
	BEGIN	SET @where = @where + ' AND '	END
	ELSE
	BEGIN	SET @where = ' WHERE '	END

	SET @where = @where + ' v.DOBFormatted = ''' + @wrkDOB + ''' ' 
END
ELSE
BEGIN
	-- If we are in here, then all three DOB elements not supplied; see if any were
	IF ISNULL(@DOBMonth, '') <> ''
	BEGIN
		IF @where <> ''
		BEGIN	SET @where = @where + ' AND '	END
		ELSE
		BEGIN	SET @where = ' WHERE '	END
	
		SET @where = @where + ' MONTH(v.DOB) = ' + @DOBMonth
	END
	IF ISNULL(@DOBDay, '') <> ''
	BEGIN
		IF @where <> ''
		BEGIN	SET @where = @where + ' AND '	END
		ELSE
		BEGIN	SET @where = ' WHERE '	END
	
		SET @where = @where + ' DAY(v.DOB)= ' + @DOBDay
	END
	IF ISNULL(@DOBYear, '') <> ''
	BEGIN
		IF @where <> ''
		BEGIN	SET @where = @where + ' AND '	END
		ELSE
		BEGIN	SET @where = ' WHERE '	END
	
		SET @where = @where + ' YEAR(v.DOB) = ' + @DOBYear
	END
END

-- If a case number was supplied, we need to join in the cases table to the view
IF ISNULL(@CaseNumber, '') <> ''
BEGIN
	IF @where <> ''
	BEGIN	SET @where = @where + ' AND '	END
	ELSE
	BEGIN	SET @where = ' WHERE '	END

	SET @from = @from + ' INNER JOIN dbo.Cases c ON v.OID = c.ServiceManID '
	SET @where = @where + ' c.OID = ' + @CaseNumber
END

-- Put a carriage return at the beginning of each of the query elements to make debugging easier
SET @where = CHAR(10) + @where


---------------------------------------------------------------------------------------------------
--	Step 3: Populate the temp table with the preliminary results.  Using the temp table, because
--			the case id data element returned has to show all the cases for the serviceman.
---------------------------------------------------------------------------------------------------

INSERT INTO #srchResults
(smOID, smName, smDOB, City, State, Rank, Unit)
EXECUTE(@select + @from + @where)

--PRINT @select
--PRINT @from
--PRINT @where


---------------------------------------------------------------------------------------------------
--	Step 4: Get the cases associated with each serviceman
---------------------------------------------------------------------------------------------------

UPDATE #srchResults
SET Cases = dbo.ufGetAllCasesByServiceManID(smOID)


---------------------------------------------------------------------------------------------------
--	Step 5: Return the results
---------------------------------------------------------------------------------------------------

SELECT smName as 'Name', smDOB as 'DOB', City, State, Rank, Unit, Cases
FROM #srchResults
ORDER BY smName, smDOB, State


RETURN(0)


ExitError:
	-- More robust error handling could be added in the future
	PRINT 'THIS RETURNED AN ERROR'
	RETURN(1)

GO


---------------------------------------------------------------------------------------------------
--	Assign appropriate permissions
--	Developers and Admins have permissions to execute through membership    
---------------------------------------------------------------------------------------------------
--GRANT EXECUTE ON dbo.FindServiceMemberByNameDOBCase TO Liberty_Users
GO



---------------------------------------------------------------------------------------------------
--	Sample Execution
---------------------------------------------------------------------------------------------------
--execute dbo.FindServiceMemberByNameDOBCase
--		@LastName = 'Smith',
--		@DOBMonth = '11',
--		@DOBDay = '02',
--		@DOBYear = '1962', 
--		@CaseNumber = NULL
